package com.kcs.fxmargin.dao;

import java.sql.*;
import java.util.*;

import com.kcs.fxmargin.bo.MainPageBO;

public class MainPageDAO {

	public MainPageBO getMenuList(MainPageBO model, Connection conn) throws Exception {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = null;
        List<MainPageBO> menuList = null;
        List<Object> resultList = null;

        try{
            sql = "with menu_list as"
            	 +" ( "
            	 +" select p.id, p.name, p.url, p.action, p.description, p.parent_menu, 1 as level, p.seq_show "
            	 +" from m_menu as p "
            	 +" where p.active_status='A' "
            	 +" and (p.parent_menu is null or p.parent_menu='') "
            	 +" union all "
            	 +" select c.id, c.name, c.url, c.action, c.description, c.parent_menu, menu.level+1, c.seq_show "
            	 +" from m_menu as c inner join menu_list as menu on c.parent_menu=menu.id "
            	 +" where c.active_status='A' "
            	 +" and c.parent_menu is not null "
            	 +" ) "
            	 +" select m.*, a.id auth_id, a.menu_id, a.role_id, a.flag "
    			 +" from menu_list m "
    			 +" left outer join m_users_auth a on m.id=a.menu_id and a.active_status='A' "
    			 +" left join m_role r on a.role_id=r.id and r.active_status='A' "
    			 +" where  m.parent_menu is null "
    			 +" or (m.parent_menu is not null and a.role_id=?) "
    			 +" and a.flag <> 0 "
    			 +" order by seq_show, level ";
            
            System.out.println(sql);
            
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, model.getRoleId());
            rs = pstmt.executeQuery();
            
            if(rs!=null){
	            menuList = new ArrayList<MainPageBO>();
	            resultList = convertRsToArrayList(rs);
	            
	            for(int i=0; i<resultList.size(); i++){
	            	HashMap<String,Object> h = (HashMap<String,Object>)resultList.get(i);
	            	MainPageBO m = new MainPageBO();
	            	m.setMlId(convertToString(h.get("id")));
	            	m.setMlName(convertToString(h.get("name")));
	            	m.setMlUrl(convertToString(h.get("url")));
	            	m.setMlAction(convertToString(h.get("action")));
	            	m.setMlDescription(convertToString(h.get("description")));
	            	m.setMlParentMenu(convertToString(h.get("parent_menu")));
	            	m.setMlLevel(convertToString(h.get("level")));
	            	m.setMlSeqShow(convertToString(h.get("seq_show")));
	            	m.setMlAuthId(convertToString(h.get("auth_id")));
	            	m.setMlMenuId(convertToString(h.get("menu_id")));
	            	m.setMlRoleId(convertToString(h.get("role_id")));
	            	m.setMlFlag(convertToString(h.get("flag")));
	            	menuList.add(m);
	            }
	            
	            model.setMenuList(menuList);
            }
            
        }catch(Exception e){
            throw e;
        }finally{
	        if(pstmt != null){
	            try{
	                pstmt.close();
	            }catch(Exception ex){
	                throw ex;
	            }
	        }
	        if(rs != null)
	            try{
	                rs.close();
	            }catch(Exception ex){
	                throw ex;
	            }
        }
       
        return model;
    }

	private ArrayList<Object> convertRsToArrayList(ResultSet rs) throws Exception {
		ArrayList<Object> resultList = null;
		ResultSetMetaData rsmd = null;
		HashMap<String,Object> h = null;
		String colName = null;
		
		resultList = new ArrayList<Object>();
		rsmd = rs.getMetaData();

		while(rs.next()){
			h = new HashMap<String,Object>();
			for (int i = 0; i < rsmd.getColumnCount(); i++) {
				colName = rsmd.getColumnName(i + 1);
				h.put(colName, rs.getObject(colName));
			}
			resultList.add(h);
		}

		return resultList;
	}
	
	public MainPageBO getUserData(MainPageBO model, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = null;
        
        try{
            sql =  "select * "
            	  +" from m_users "
            	  +" where user_id = ?";
            
            System.out.println(sql);
            System.out.println("<- userId: "+model.getUserId());
            
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, model.getUserId());
            rs = pstmt.executeQuery();
            
            if(rs != null){      	
	            while(rs.next()){
	            	model.setId(rs.getString("id"));
	            	model.setUserId(rs.getString("user_id"));
	            	model.setUserName(rs.getString("user_name"));
	            	model.setRoleId(rs.getString("role_id"));
	            	model.setActiveStatus(rs.getString("active_status"));
	            	model.setCreateBy(rs.getString("create_by"));
	            	model.setCreateDt(rs.getString("create_dt"));
	            	model.setUpdateBy(rs.getString("update_by"));
	            	model.setUpdateDt(rs.getString("update_dt"));
	            }
            }
            
        }catch(Exception e){
            throw e;
        }finally{
	        if(pstmt != null){
	            try{
	                pstmt.close();
	            }catch(Exception ex){
	                throw ex;
	            }
	        }
	        if(rs != null)
	            try{
	                rs.close();
	            }catch(Exception ex){
	                throw ex;
	            }
        }
       
        return model;
	}
	
	public String convertToString(Object ob){
		return (ob==null)?"":ob.toString();
	}
	
}